const db = require('../config/db');

const ProductModel = {
  // 获取所有商品
  getAllProducts: async (options = {}) => {
    let query = 'SELECT p.*, c.name as category_name FROM products p LEFT JOIN categories c ON p.category_id = c.id';
    const params = [];
    
    if (options.categoryId) {
      query += ' WHERE p.category_id = ?';
      params.push(options.categoryId);
    }
    
    if (options.search) {
      const searchCondition = options.categoryId 
        ? ' AND p.name LIKE ?' 
        : ' WHERE p.name LIKE ?';
      query += searchCondition;
      params.push(`%${options.search}%`);
    }
    
    query += ' ORDER BY p.created_at DESC';
    
    if (options.limit) {
      query += ' LIMIT ?';
      params.push(options.limit);
    }
    
    const [rows] = await db.execute(query, params);
    return rows;
  },
  
  // 获取商品详情
  getProductById: async (id) => {
    const [rows] = await db.execute(
      'SELECT p.*, c.name as category_name FROM products p LEFT JOIN categories c ON p.category_id = c.id WHERE p.id = ?',
      [id]
    );
    return rows[0] || null;
  },
  
  // 创建商品
  createProduct: async (productData) => {
    const { category_id, name, description, price, stock, image } = productData;
    const [result] = await db.execute(
      'INSERT INTO products (category_id, name, description, price, stock, image) VALUES (?, ?, ?, ?, ?, ?)',
      [category_id, name, description, price, stock, image]
    );
    return result.insertId;
  },
  
  // 更新商品
  updateProduct: async (id, updateData) => {
    const { name, description, price, stock, image, is_active } = updateData;
    const [result] = await db.execute(
      'UPDATE products SET name = ?, description = ?, price = ?, stock = ?, image = ?, is_active = ? WHERE id = ?',
      [name, description, price, stock, image, is_active, id]
    );
    return result.affectedRows > 0;
  },
  
  // 删除商品
  deleteProduct: async (id) => {
    const [result] = await db.execute('DELETE FROM products WHERE id = ?', [id]);
    return result.affectedRows > 0;
  }
};

module.exports = ProductModel;